--    Program: 		CAPARIMP_RECEIPT_DIST.sql
--    Description:  Import AR RECEIPT DIST information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_line_id				number;
   l_source_id				number;
   
   lv_FileName		VARCHAR2(100)	:=	'logd.log';
   lv_PathName		VARCHAR2(100)	:=	'/tmp';
   lv_UTL_File		UTL_FILE.File_Type;
   lv_counter		VARCHAR(100);
                    		                 		
   cursor cur_ar_receipt_dist is
      select * from CRST_AR_DISTRIBUTIONS_ALL
      --WHERE LINE_ID = '66480'
      for update;
      

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   lv_counter := 0;
   
   for cur in cur_ar_receipt_dist loop   	
     l_err_msg_tmp := '';
     
     lv_counter := lv_counter + 1;
     
     lv_UTL_File := UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
     UTL_FILE.PUT_LINE(lv_UTL_File, lv_counter);
	 UTL_FILE.FClose(lv_UTL_File);
	 
     if cur.source_table = 'CRH' then
       select CAPARIMP_MAPPING.GetCashReceiptHistoryIDMapping(cur.source_id)
       into   l_source_id	
       from   dual;
     elsif cur.source_table = 'RA' then
       select CAPARIMP_MAPPING.GetReceivableApplicationIDMap(cur.source_id)
       into   l_source_id	
       from   dual;
     elsif cur.source_table = 'ADJ' then
       select CAPARIMP_MAPPING.GetAdjustmentIDMapping(cur.source_id)
       into   l_source_id	
       from   dual;
     elsif cur.source_table = 'MCD' then
       select CAPARIMP_MAPPING.GetMiscCashDistributionIDMap(cur.source_id)
       into   l_source_id	
       from   dual;
     else
       l_source_id := cur.source_id;	
     end if;   
     
     SELECT AR_DISTRIBUTIONS_S.NEXTVAL 
     INTO l_line_id
     FROM DUAL;
     
     update CRST_AR_DISTRIBUTIONS_ALL
     set    line_id_n = l_line_id
     where  line_id = cur.line_id;
     
     begin
       -- import data
       insert into AR_DISTRIBUTIONS_ALL
       (
          LINE_ID                   ,
          SOURCE_ID                 ,
          SOURCE_TABLE              ,
          SOURCE_TYPE               ,
          CODE_COMBINATION_ID       ,
          AMOUNT_DR                 ,
          AMOUNT_CR                 ,
          ACCTD_AMOUNT_DR           ,
          ACCTD_AMOUNT_CR           ,
          CREATION_DATE             ,
          CREATED_BY                ,
          LAST_UPDATED_BY           ,
          LAST_UPDATE_DATE          ,
          LAST_UPDATE_LOGIN         ,
          ORG_ID                    ,
          SOURCE_TABLE_SECONDARY    ,
          SOURCE_ID_SECONDARY       ,
          CURRENCY_CODE             ,
          CURRENCY_CONVERSION_RATE  ,
          CURRENCY_CONVERSION_TYPE  ,
          CURRENCY_CONVERSION_DATE  ,
          TAXABLE_ENTERED_DR        ,
          TAXABLE_ENTERED_CR        ,
          TAXABLE_ACCOUNTED_DR      ,
          TAXABLE_ACCOUNTED_CR      ,
          TAX_LINK_ID               ,
          THIRD_PARTY_ID            ,
          THIRD_PARTY_SUB_ID        ,
          REVERSED_SOURCE_ID        ,
          TAX_CODE_ID               ,
          LOCATION_SEGMENT_ID       ,
          SOURCE_TYPE_SECONDARY     ,
          TAX_GROUP_CODE_ID           
       )
       values
       (
        l_line_id                     ,
        l_source_id                   ,
        cur.SOURCE_TABLE              ,
        cur.SOURCE_TYPE               ,
        CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
        cur.AMOUNT_DR                 ,
        cur.AMOUNT_CR                 ,
        cur.ACCTD_AMOUNT_DR           ,
        cur.ACCTD_AMOUNT_CR           ,
        cur.CREATION_DATE             ,
        CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
        CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
        cur.LAST_UPDATE_DATE          ,
        cur.LAST_UPDATE_LOGIN         ,
        CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
        cur.SOURCE_TABLE_SECONDARY    ,
        cur.SOURCE_ID_SECONDARY       ,
        cur.CURRENCY_CODE             ,
        cur.CURRENCY_CONVERSION_RATE  ,
        cur.CURRENCY_CONVERSION_TYPE  ,
        cur.CURRENCY_CONVERSION_DATE  ,
        cur.TAXABLE_ENTERED_DR        ,
        cur.TAXABLE_ENTERED_CR        ,
        cur.TAXABLE_ACCOUNTED_DR      ,
        cur.TAXABLE_ACCOUNTED_CR      ,
        cur.TAX_LINK_ID               ,
        cur.THIRD_PARTY_ID            ,
        cur.THIRD_PARTY_SUB_ID        ,
        cur.REVERSED_SOURCE_ID        ,
        cur.TAX_CODE_ID               ,
        cur.LOCATION_SEGMENT_ID       ,
        cur.SOURCE_TYPE_SECONDARY     ,
        cur.TAX_GROUP_CODE_ID                                
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_RECEIPT_DIST', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
